package com.pms.utils;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.pms.platform.resource.ResourceManager;


public class DbUtility {

	private static final String TRACE_ID = "DbUtility";
	public static long getNextSequence(Connection aConnection, String aTableName)  throws SQLException{

		String db="sqlserver";
		if(db.equalsIgnoreCase("oracle")) {
			return getOracleNextSequenceId(aConnection, aTableName);
		}
		if(db.equalsIgnoreCase("sqlserver")) {
			return getSqlNextSequenceId(aTableName);
		}
		return 1;
	}

	/** Get the next valid sequence id
	 *
	 * @param aConnection   Connection to use
	 * @param aTableName    Name of the table for which the sequence needs to be generated
	 *
	 * @exception SQLException  If a database access error occurs
	 *
	 */
	private synchronized static int getOracleNextSequenceId(Connection aConnection, String aTableName) throws SQLException {


		String nextSequenceQuery = "Select " + aTableName + "_seq.NextVal from dual";
		System.out.println("getOracleNextSequenceId() : executing query => " + nextSequenceQuery );

		Statement stmt=aConnection.createStatement();
		ResultSet resultSet = stmt.executeQuery(nextSequenceQuery);

		if(resultSet.next() == false)
			throw new SQLException("CategoryIO.getNextCategoryId() : No result getting sequence with => " + nextSequenceQuery);

		int id=resultSet.getInt(1);

		resultSet.close();
		stmt.close();

		return id;
	}




	private synchronized static int getSqlNextSequenceId(String aTableName) throws SQLException {
		int id = 0;
		Connection conn = null;
		try {
			conn = ResourceManager.getConnection();
			System.out.println("--------------------------This Squence val is With SP NOW-------------------------");

			String str="{ call cspSequenceGernerator '"+aTableName+"'}";
			System.out.println("@@@-- rspAgentwiseTicketCollection store procedure->"+str);
			CallableStatement cs = conn.prepareCall(str);
			ResultSet resultSet = cs.executeQuery();


			if(resultSet == null || resultSet.next() == false) {
				System.out.println("DBUtility.getSqlNextSequenceId() : Creating sequernce for => " + aTableName);
				String insertStatement = "Insert into SEQUENCE_TABLE values('" + aTableName.toUpperCase() + "_SEQ', 1)";
				Statement stmt = conn.createStatement();
				int rows = stmt.executeUpdate(insertStatement);
				id=1;
				System.out.println("Number of Rows effected - " + rows);
				stmt.close();
			} else {
				id = resultSet.getInt(1);
			}

			resultSet.close();
		} finally {
			ResourceManager.freeConnection(conn,"DbUtility");
		}
		return id;
	}



}
